import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

air_warring_router = APIRouter(prefix="/material/tsAirwarring", tags=["环境报警设定管理"],
                               dependencies=[Depends(oauth2_scheme)])


class AirWarringSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fairitem: Optional[str] = Field("", description="空气项目")


@air_warring_router.get("/list", summary="环境安全--报警设定查询接口")
def list_airwarring(search: AirWarringSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_ts_airwarring a,s_ts_airitem b"
    sql = sql + " where 1=1"
    sql = sql + " and a.fairitem=b.fairitem"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fairitem != "":
        sql = sql + " and a.fairitem = :fairitem "
        param["fairitem"] = search.fairitem
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fwarringno,a.fsite_no,a.fscore_flag,a.fairitem,a.fbegnum,"
    sql = sql + " a.fendnum,a.fdescribe,a.flevelno,a.fsendmsg,a.fsendemail,"
    sql = sql + " a.fendnum,a.fdescribe,a.flevelno,a.fsendmsg,a.fsendemail,"
    sql = sql + " b.fariname"
    sql = sql + " from i_ts_airwarring a,s_ts_airitem b"
    sql = sql + " where 1=1"
    sql = sql + " and a.fairitem=b.fairitem"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fairitem != "":
        sql = sql + " and a.fairitem = :fairitem "
        param["fairitem"] = search.fairitem
    if search.order == "desc":
        sql = sql + " order by  a.fwarringno desc "
    else:
        sql = sql + " order by  a.fwarringno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fentryid,b.fwarringno,b.fstorageflag"
    sql_3 = sql_3 + " from i_ts_airwarring a,i_ts_airwarring_point b  "
    sql_3 = sql_3 + " where a.fwarringno=b.fwarringno"
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fairitem != "":
        sql_3 = sql_3 + " and fairitem = :fairitem "
        param["fairitem"] = search.fairitem
    sql_3 = sql_3 + " order by b.fwarringno,b.fentryid"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        fupddate = ""
        try:
            fupddate = data[key][12].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)
        if (fupddate == ""):
            fupddate = None
        d1 = {}
        d1['fwarringno'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fscoreFlag'] = data[key][2]
        d1['fairitem'] = data[key][3]
        d1['fbegnum'] = data[key][4]
        d1['fendnum'] = data[key][5]
        d1['fdescribe'] = data[key][6]
        d1['flevelno'] = data[key][7]
        d1['fsendmsg'] = data[key][8]
        d1['fsendemail'] = data[key][9]
        d1['fstate'] = data[key][10]
        d1['foperator'] = data[key][11]
        d1['fupddate'] = fupddate
        d1['fariname'] = data[key][13]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][1]) == str(sbillno)):
                d2 = {}
                d2['fentryid'] = data_detail[j1][0]
                d2['fwarringno'] = data_detail[j1][1]
                d2['fstorageflag'] = data_detail[j1][2]
                detail_list.append(d2)

        d1["atsPoints"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class AirWarringPoint(BaseModel):
    fstorageflag: int = Field(..., description="存储标志")
    label: str = Field(..., description="标签")
    value: str = Field(..., description="值")


class AirWarringCreate(BaseModel):
    """环境安全-报警设定新增请求参数"""
    fsiteNo: str = Field(..., description="站点编号")
    fairitem: str = Field(..., description="空气项目")
    fscoreFlag: int = Field(..., description="评分标志")
    fbegnum: float = Field(..., description="起始值")
    fendnum: float = Field(..., description="结束值")
    fdescribe: str = Field(..., description="描述")
    fsendmsg: int = Field(..., description="是否发送短信(0/1)")
    flevelno: str = Field(..., description="等级编号")
    fsendemail: int = Field(..., description="是否发送邮件(0/1)")
    fstate: int = Field(..., description="状态")
    atsPoints: List[AirWarringPoint] = Field(..., description="报警点位列表")
    fwarringno: int = Field(None, description="设定编号，自动生成")


@air_warring_router.post("/create", summary="环境安全--报警设定新增接口")
def create_airwarring(create: AirWarringCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fsiteNo = create.fsiteNo
        fairitem = create.fairitem
        userid = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fairitem from i_ts_airwarring where fairitem= :fairitem  and fsite_No= :fsiteNo "
            data = db.execute(text(sql), {
                "fairitem": fairitem,
                "fsiteNo": fsiteNo,
            }).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="该项目已设定")
            else:
                # 先取单号
                fwarringno = "0"
                sql = "select nextval('a_ts_airwarring_fwarringno_seq') as last_value "
                res = db.execute(text(sql)).fetchall()
                fwarringno = res[0][0]
                detail_list = []

                jsondata = create.atsPoints
                for data in jsondata:
                    sql = "insert into i_ts_airwarring_point(fwarringno,fstorageflag)"
                    sql = sql + " values( :fwarringno , :fstorageflag ) ;"
                    db.execute(text(sql), {
                        "fwarringno": fwarringno,
                        "fstorageflag": data.fstorageflag,
                    })
                    # 返回明细
                    d2 = {}
                    d2['fstorageflag'] = data.fstorageflag
                    d2['label'] = data.label
                    d2['value'] = data.value
                    detail_list.append(d2)
                sql = "insert into i_ts_airwarring(fwarringno,fsite_no,fscore_flag,fairitem,fbegnum,fendnum,fdescribe,flevelno,fsendmsg,fsendemail,fstate,fupddate,foperator)"
                sql = sql + " values( :fwarringno , :fsiteNo , :fscoreFlag , :fairitem , :fbegnum , :fendnum , :fdescribe , :flevelno , :fsendmsg , :fsendemail , :fstate ,now(), :userid );"
                db.execute(text(sql), {
                    "fwarringno": fwarringno,
                    "fsiteNo": fsiteNo,
                    "fscoreFlag": create.fscoreFlag,
                    "fairitem": fairitem,
                    "fbegnum": create.fbegnum,
                    "fendnum": create.fendnum,
                    "fdescribe": create.fdescribe,
                    "flevelno": create.flevelno,
                    "fsendmsg": create.fsendmsg,
                    "fsendemail": create.fsendemail,
                    "fstate": create.fstate,
                    "userid": userid,

                })
                # 返回
                d1 = {}
                d1['fsiteNo'] = fsiteNo
                d1['fscoreFlag'] = create.fscoreFlag
                d1['fairitem'] = fairitem
                d1['fbegnum'] = create.fbegnum
                d1['fendnum'] = create.fendnum
                d1['fdescribe'] = create.fsendmsg
                d1['fsendmsg'] = create.fsendmsg
                d1['flevelno'] = create.flevelno
                d1['fsendemail'] = create.fsendemail
                d1['fstate'] = create.fstate
                d1["atsPoints"] = detail_list
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@air_warring_router.post("/update", summary="报警设定修改接口")
def update_airwarring(update: AirWarringCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fwarringno = update.fwarringno
        with db.begin():
            sql = "delete from i_ts_airwarring_point where fwarringno= ：fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno,
            })
            atsPoints = update.atsPoints
            for point in atsPoints:
                sql = "insert into i_ts_airwarring_point(fwarringno,fstorageflag)"
                sql = sql + " values( :fwarringno , :fstorageflag ) ;"
                db.execute(text(sql), {
                    "fwarringno": fwarringno,
                    "fstorageflag": point.fstorageflag,
                })
            sql = "update i_ts_airwarring "
            sql = sql + " set fscore_flag= :fscoreFlag ,fairitem= :fairitem "
            sql = sql + " ,fbegnum= :fbegnum ,fendnum= :fendnum ,fdescribe= :fdescribe "
            sql = sql + " ,flevelno= :flevelno ,fsendmsg= :fsendmsg ,fsendemail= :fsendemail "
            sql = sql + " ,fstate= :fstate ,fupddate=now(),foperator= :userid "
            sql = sql + " where fwarringno= :fwarringno "
            sql = sql + " and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fscoreFlag": update.fscoreFlag,
                "fairitem": update.fairitem,
                "fbegnum": update.fbegnum,
                "fendnum": update.fendnum,
                "fdescribe": update.fdescribe,
                "flevelno": update.flevelno,
                "fsendmsg": update.fsendmsg,
                "fsendemail": update.fsendemail,
                "fstate": update.fstate,
                "userid": user_id,
                "fwarringno": fwarringno,
                "fsiteNo": update.fsiteNo,
            })
        return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@air_warring_router.post("/delete/{fwarringno}", summary="报警设定删除接口")
def delete_air_warring(fwarringno: int = Path(description="设定编号"), db: Session = Depends(get_db),
                       token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "delete from i_ts_airwarring where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno
            })
            sql = "delete from i_ts_airwarring_set where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
